﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SoufunData.DB;

namespace SoufunData
{
    public partial class ExToDbOfficeInfoForm : DevExpress.XtraEditors.XtraForm
    {
        public ExToDbOfficeInfoForm()
        {
            InitializeComponent();
        }

        private void btn_excelPath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {

            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            string fileName = openFileDialog1.FileName;
            btn_excelPath.Text = fileName;

            gridControl2.DataSource = null;
            gridView2.Columns.Clear();
            gridView2.GroupPanelText = "请选择Excel文件";
            if (fileName == "")
            {
                return;
            }
            DataSet ds = Common.LoadDataFromExcel(fileName);
            if (ds == null || ds.Tables.Count == 0)
            {
                string strError = "读取Excel时发生错误，请核对：\r\n ";
                strError += "1，导入时请在Excel中关闭想要导入的文件！\r\n";
                strError += "2，导入的Excel表中的工作表名称必须为Sheet1,否者程序无法读取！\r\n";
                strError += "3，请确保您的计算机安装了Microsoft Office！\r\n";
                strError += "4，请确保导入的Excel文件正常！";
                XtraMessageBox.Show(strError);
                btn_excelPath.Text = "";
                return;
            }            
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {
                btn_excelPath.Text = "";
                return;
            }
            DataTable dt = ds.Tables[0].Copy();
            dt.Clear();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                if (row[0].ToString() == "" && row[1].ToString() == ""
                    && row[2].ToString() == "")
                {
                    continue;
                }
                dt.Rows.Add(row.ItemArray);
            }
            gridControl2.DataSource = dt;
            gridView2.GroupPanelText = "共有" + dt.Rows.Count + "条数据";
        }


        private void AddData()
        {
           
            DataTable dtOld = (DataTable)gridControl2.DataSource;

            DataTable dt = dtOld.Copy();
            dt.Rows.Clear();
            foreach (DataRow dataRow in dtOld.Rows)
            { 
                dt.Rows.Add(dataRow.ItemArray);
            }

            SqlClass sqlClass = new SqlClass();
            List<string> existProject = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                string proName = row["项目名称"].ToString();
                if (sqlClass.ExistProjectName(proName, "tofficedatainfo"))
                {
                    existProject.Add(proName);
                }
            }
            if (existProject.Count > 0)
            {
                StringBuilder strWarming = new StringBuilder();
                strWarming.Append("您选择的Excel表格中的以下项目在数据库中已存在,不能再次添加：\r\n");
                foreach (string sproName in existProject)
                {
                    strWarming.Append(sproName);
                    strWarming.Append("\r\n");
                }
                XtraMessageBox.Show(strWarming.ToString());
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    btn_excelPath.Enabled = true;
                    btn_add.Enabled = true;
                }));
                return;
            }


            int insertRowsCount = 500;//一次性插入数据库行数
            int succeedrows = 0;//成功插入数据行数
            StringBuilder strSql = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i==12)
                {
                    Console.WriteLine("222");
                }
                DataRow row = dt.Rows[i]; 
                string strProName = row["项目名称"].ToString();
                string strAddress = row["地址"].ToString();
                string strXzq = row["行政区"].ToString();
                string strBoard = row["版块"].ToString();
                string strBuildingCompany = row["开发商"].ToString();
                string strOverTime = row["建成时间"].ToString();
                strOverTime = DateTime.Parse(strOverTime).ToString("yyyy/MM/dd");
                string strEnterTime = row["入驻时间"].ToString();
                strEnterTime = DateTime.Parse(strEnterTime).ToString("yyyy/MM/dd");
                string strProjectType = row["项目形态"].ToString();
                string strOutFacade = row["项目外立面"].ToString();
                string strBuildingLevel = row["写字楼档次"].ToString();
                string strCoverArea = row["占地面积"].ToString();
                if (strCoverArea.Trim() == "" || strCoverArea.Trim() == "-")
                {
                    strCoverArea = "-1";
                }
                string strBuildingArea = row["项目建筑面积"].ToString();
                if (strBuildingArea.Trim() == "" || strBuildingArea.Trim() == "-")
                {
                    strBuildingArea = "-1";
                }
                string strOfficeArea = row["实际办公面积"].ToString();
                if (strOfficeArea.Trim() == "" || strOfficeArea.Trim() == "-")
                {
                    strOfficeArea = "-1";
                }
                string strOperator = row["运营商"].ToString();
                string strPropertyCompany = row["物业管理公司"].ToString();
                string strLiftCount = row["电梯数量"].ToString();
                if (strLiftCount.Trim() == "" || strLiftCount.Trim() == "-")
                {
                    strLiftCount = "-1";
                }
                string strLiftBrand = row["电梯品牌"].ToString();
                string strConditioner = row["空调类型及品牌"].ToString();
                string strLobbyArea = row["大堂面积"].ToString();
                string strLobbyHeight = row["大堂层高"].ToString();
                string strStandardFloorCount = row["标准层层数"].ToString();
                if (strStandardFloorCount.Trim() == "" 
                    || strStandardFloorCount.Trim() == "-")
                {
                    strStandardFloorCount = "-1";
                }
                string strStandardFloorArea = row["标准层面积"].ToString();
                if (strStandardFloorArea.Trim() == ""
                   || strStandardFloorArea.Trim() == "-")
                {
                    strStandardFloorArea = "-1";
                }
                string strCutAreaCount = row["分割面积段"].ToString();
                string strIsForSale = row["自持或出售"].ToString();
                string strSaleState = row["销售状态"].ToString();
                string strSaleType = row["出售形式"].ToString();
                string strSalePrice = row["售价"].ToString();
                if (strSalePrice.Trim() == ""
                 || strSalePrice.Trim() == "-")
                {
                    strSalePrice = "-1";
                } 
                string strHirePrice = row["租金"].ToString();
                if (strHirePrice.Trim() == ""
                 || strHirePrice.Trim() == "-")
                {
                    strHirePrice = "-1";
                } 
                string strPropertyPrice = row["物业费"].ToString();
                if (strPropertyPrice.Trim() == ""
                 || strPropertyPrice.Trim() == "-")
                {
                    strPropertyPrice = "-1";
                } 

                string strParkingCount = row["停车位"].ToString();
                string strParkingPrice = row["停车费"].ToString();
                string strTrimState = row["装修状态"].ToString();
                string strEnterRate = row["入住率"].ToString();
                string strMainEnterCompany = row["主要入驻企业"].ToString();
                string strElseConfig = row["其他配置"].ToString();
                string strBusinessCircle = row["所属商圈"].ToString();
                string strBusinessConfigAround = row["周边商业配套"].ToString();
                string strTrafficConfigAround = row["周边交通配套"].ToString();
                string strPropertyScore = row["物业满意度打分"].ToString();
                string strPropertyReport = row["物业满意度报告"].ToString();
                
                if (i % insertRowsCount == 0)
                {
                    strSql = new StringBuilder();
                    strSql.Append("Insert into tofficedatainfo (");
                    strSql.Append("ProjectName,");
                    strSql.Append("Address,");
                    strSql.Append("AdministratiVeregion,");
                    strSql.Append("Board,");
                    strSql.Append("BuildingCompany,");
                    strSql.Append("OverTime,");
                    strSql.Append("EnterTime,");
                    strSql.Append("ProjectType,");
                    strSql.Append("OutFacade,");
                    strSql.Append("BuildingLevel,");
                    strSql.Append("CoverArea,");
                    strSql.Append("BuildingArea,");
                    strSql.Append("OfficeArea,");
                    strSql.Append("Operator,");
                    strSql.Append("PropertyCompany,");
                    strSql.Append("LiftCount,");
                    strSql.Append("LiftBrand,");
                    strSql.Append("Conditioner,");
                    strSql.Append("LobbyArea,");
                    strSql.Append("LobbyHeight,");
                    strSql.Append("StandardFloorCount,");
                    strSql.Append("StandardFloorArea,");
                    strSql.Append("CutAreaCount,");
                    strSql.Append("IsForSale,");
                    strSql.Append("SaleState,");
                    strSql.Append("SaleType,");
                    strSql.Append("SalePrice,");
                    strSql.Append("HirePrice,");
                    strSql.Append("PropertyPrice,");
                    strSql.Append("ParkingCount,");
                    strSql.Append("ParkingPrice,");
                    strSql.Append("TrimState,");
                    strSql.Append("EnterRate,");
                    strSql.Append("MainEnterCompany,");
                    strSql.Append("ElseConfig,");
                    strSql.Append("BusinessCircle,");
                    strSql.Append("BusinessConfigAround,");
                    strSql.Append("TrafficConfigAround,");
                    strSql.Append("PropertyScore,");
                    strSql.Append("PropertyReport)"); 

                    strSql.Append(" value  ");
                }
                strSql.Append("(");
                strSql.Append("'");
                strSql.Append(strProName);
                strSql.Append("','");
                strSql.Append(strAddress);
                strSql.Append("','");
                strSql.Append(strXzq);
                strSql.Append("','");
                strSql.Append(strBoard);
                strSql.Append("','");
                strSql.Append(strBuildingCompany);
                strSql.Append("','");
                strSql.Append(strOverTime);
                strSql.Append("','");
                strSql.Append(strEnterTime);
                strSql.Append("','");
                strSql.Append(strProjectType);
                strSql.Append("','");
                strSql.Append(strOutFacade);
                strSql.Append("','");
                strSql.Append(strBuildingLevel);
                strSql.Append("',");
                strSql.Append(strCoverArea);
                strSql.Append(",");
                strSql.Append(strBuildingArea);
                strSql.Append(",");
                strSql.Append(strOfficeArea);
                strSql.Append(",'");
                strSql.Append(strOperator);              
                strSql.Append("','");
                strSql.Append(strPropertyCompany);
                strSql.Append("',");
                strSql.Append(strLiftCount);
                strSql.Append(",'");
                strSql.Append(strLiftBrand);
                strSql.Append("','");
                strSql.Append(strConditioner);
                strSql.Append("',");
                strSql.Append(strLobbyArea);
                strSql.Append(",");
                strSql.Append(strLobbyHeight);
                strSql.Append(",");
                strSql.Append(strStandardFloorCount);
                strSql.Append(",");
                strSql.Append(strStandardFloorArea);
                strSql.Append(",'");
                strSql.Append(strCutAreaCount);
                strSql.Append("','");
                strSql.Append(strIsForSale);
                strSql.Append("','");
                strSql.Append(strSaleState);
                strSql.Append("','");
                strSql.Append(strSaleType);
                strSql.Append("',");
                strSql.Append(strSalePrice);
                strSql.Append(",");
                strSql.Append(strHirePrice);
                strSql.Append(",");
                strSql.Append(strPropertyPrice);
                strSql.Append(",'");
                strSql.Append(strParkingCount);
                strSql.Append("','");
                strSql.Append(strParkingPrice);
                strSql.Append("','");
                strSql.Append(strTrimState);
                strSql.Append("','");
                strSql.Append(strEnterRate);
                strSql.Append("','");
                strSql.Append(strMainEnterCompany);
                strSql.Append("','");
                strSql.Append(strElseConfig);
                strSql.Append("','");
                strSql.Append(strBusinessCircle);
                strSql.Append("','");
                strSql.Append(strBusinessConfigAround);              
                strSql.Append("','");
                strSql.Append(strTrafficConfigAround);
                strSql.Append("','");
                strSql.Append(strPropertyScore);
                strSql.Append("','");
                strSql.Append(strPropertyReport);              
                strSql.Append("'),");

                if ((i + 1) % insertRowsCount == 0
                    || i == dt.Rows.Count - 1)
                {
                    strSql.Remove(strSql.Length - 1, 1);
                    try
                    {
                        succeedrows += DbHelper.ExecuteSql(strSql.ToString());
                    }
                    catch (Exception ex)
                    {
                        XtraMessageBox.Show(ex.Message);
                    }

                }
            }

            if (dt.Rows.Count == succeedrows)
            {
                XtraMessageBox.Show("添加成功！");
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    gridView2.GroupPanelText = "请选择想要导入的Excel表！";
                }));
            }
            else
            {
                int failedCount = dt.Rows.Count - succeedrows;
                XtraMessageBox.Show("共记" + dt.Rows.Count + "条数据，其中"
                    + failedCount + "条数据添加失败！");
            }
            BeginInvoke(new MethodInvoker(() =>
            {
               
                btn_excelPath.Enabled = true;
                btn_add.Enabled = true;
            }));
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            if (btn_excelPath.Text.Trim()=="")
            {
                XtraMessageBox.Show("请先选择Excel！");
                return;
            }
            if (!CheckDataValid())
            {
                return;
            }
             
            btn_excelPath.Enabled = false;
            btn_add.Enabled = false;
            Thread t = new Thread(AddData) { IsBackground = true };
            t.Start();
        }

        #region 数据完整性检测
        private bool CheckDataValid()
        {
            DataTable dt = (DataTable)gridControl2.DataSource;
            StringBuilder strErrors = new StringBuilder();
           
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strProName = dr["项目名称"].ToString();
                string strOverTime = dr["建成时间"].ToString();
                string strEnterTime = dr["入驻时间"].ToString();
                string strCoverArea = dr["占地面积"].ToString();
                string strBuildingArea = dr["项目建筑面积"].ToString();
                string strOfficeArea = dr["实际办公面积"].ToString();
                string strLiftCount = dr["电梯数量"].ToString();
                string strStandardFloorCount = dr["标准层层数"].ToString();
                string strStandardFloorArea = dr["标准层面积"].ToString();
                string strSalePrice = dr["售价"].ToString();
                string strHirePrice = dr["租金"].ToString();
                string strPropertyPrice = dr["物业费"].ToString();
               
  
                if (strProName.Trim() == "")
                {
                    strErrors.Append("第"+(i+2)+"行数据中的【项目名称】为空！\r\n"); 
                }
                DateTime dateTemp;
                if (!DateTime.TryParse(strOverTime,out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【建成时间】不是正确的时间格式！\r\n");
                }
                if (!DateTime.TryParse(strEnterTime, out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【入驻时间】不是正确的时间格式！\r\n");
                }
                float floatTemp;
                if (strCoverArea.Trim()!="" &&
                    strCoverArea!="-"&&
                    !float.TryParse(strCoverArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【占地面积】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if ( strBuildingArea.Trim()!="" &&
                     strBuildingArea != "-" &&
                    !float.TryParse(strBuildingArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【项目建筑面积】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strOfficeArea.Trim() != "" &&
                  strOfficeArea != "-" &&
                 !float.TryParse(strOfficeArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【实际办公面积】的数据格式有误，不是正确的数字格式！\r\n");
                }
                int intTemp;
                if (!int.TryParse(strLiftCount,out intTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【电梯数量】的数据格式有误，不是正确的整数格式！\r\n");
                }
                if (!int.TryParse(strStandardFloorCount, out intTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【标准层层数】的数据格式有误，不是正确的整数格式！\r\n");
                }
                if (strStandardFloorArea.Trim() != "" &&
                strStandardFloorArea != "-" &&
               !float.TryParse(strStandardFloorArea, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【标准层面积】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strSalePrice.Trim() != "" &&
                strSalePrice != "-" &&
               !float.TryParse(strSalePrice, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【售价】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strHirePrice.Trim() != "" &&
                strHirePrice != "-" &&
               !float.TryParse(strHirePrice, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【租金】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strPropertyPrice.Trim() != "" &&
                strPropertyPrice != "-" &&
               !float.TryParse(strPropertyPrice, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【物业费】的数据格式有误，不是正确的数字格式！\r\n");
                }

               
            }
            if (strErrors.ToString() != "")
            {                
                XtraMessageBox.Show("您选择的Excel中的数据存在以下错误：\r\n"+
                    strErrors);
                return false;
            }              
            return true;
        }
         
        private bool CheckExcelColumnsName(DataTable dt)
        {
            StringBuilder strErrors = new StringBuilder();
            if (!dt.Columns.Contains("项目名称"))
            {
                strErrors.Append("【项目名称】、");
            }
            if (!dt.Columns.Contains("地址"))
            {
                strErrors.Append("【地址】、");
            }
            if (!dt.Columns.Contains("行政区"))
            {
                strErrors.Append("【行政区】、");
            }
            if (!dt.Columns.Contains("版块"))
            {
                strErrors.Append("【版块】、");
            }
            if (!dt.Columns.Contains("开发商"))
            {
                strErrors.Append("【开发商】、");
            }
            if (!dt.Columns.Contains("建成时间"))
            {
                strErrors.Append("【建成时间】、");
            }
            if (!dt.Columns.Contains("入驻时间"))
            {
                strErrors.Append("【入驻时间】、");
            }
            if (!dt.Columns.Contains("项目形态"))
            {
                strErrors.Append("【项目形态】、");
            }
            if (!dt.Columns.Contains("项目外立面"))
            {
                strErrors.Append("【项目外立面】、");
            }
            if (!dt.Columns.Contains("写字楼档次"))
            {
                strErrors.Append("【写字楼档次】、");
            }
            if (!dt.Columns.Contains("占地面积"))
            {
                strErrors.Append("【占地面积】");
            }
            if (!dt.Columns.Contains("项目建筑面积"))
            {
                strErrors.Append("【项目建筑面积】");
            }
            if (!dt.Columns.Contains("实际办公面积"))
            {
                strErrors.Append("【实际办公面积】");
            }
            if (!dt.Columns.Contains("运营商"))
            {
                strErrors.Append("【运营商】");
            }
            if (!dt.Columns.Contains("物业管理公司"))
            {
                strErrors.Append("【物业管理公司】");
            }
            if (!dt.Columns.Contains("电梯数量"))
            {
                strErrors.Append("【电梯数量】");
            }
            if (!dt.Columns.Contains("电梯品牌"))
            {
                strErrors.Append("【电梯品牌】");
            }
            if (!dt.Columns.Contains("空调类型及品牌"))
            {
                strErrors.Append("【空调类型及品牌】");
            }
            if (!dt.Columns.Contains("大堂面积"))
            {
                strErrors.Append("【大堂面积】");
            }
            if (!dt.Columns.Contains("大堂层高"))
            {
                strErrors.Append("【大堂层高】");
            }
            if (!dt.Columns.Contains("标准层层数"))
            {
                strErrors.Append("【标准层层数】");
            }
            if (!dt.Columns.Contains("标准层面积"))
            {
                strErrors.Append("【标准层面积】");
            }
            if (!dt.Columns.Contains("分割面积段"))
            {
                strErrors.Append("【分割面积段】");
            }
            if (!dt.Columns.Contains("自持或出售"))
            {
                strErrors.Append("【自持或出售】");
            }
            if (!dt.Columns.Contains("销售状态"))
            {
                strErrors.Append("【销售状态】");
            }
            if (!dt.Columns.Contains("出售形式"))
            {
                strErrors.Append("【出售形式】");
            }
            if (!dt.Columns.Contains("售价"))
            {
                strErrors.Append("【售价】");
            }
            if (!dt.Columns.Contains("租金"))
            {
                strErrors.Append("【租金】");
            }
            if (!dt.Columns.Contains("物业费"))
            {
                strErrors.Append("【物业费】");
            }
            if (!dt.Columns.Contains("停车位"))
            {
                strErrors.Append("【停车位】");
            }
            if (!dt.Columns.Contains("停车费"))
            {
                strErrors.Append("【停车费】");
            }
            if (!dt.Columns.Contains("装修状态"))
            {
                strErrors.Append("【装修状态】");
            }
            if (!dt.Columns.Contains("入住率"))
            {
                strErrors.Append("【入住率】");
            }
            if (!dt.Columns.Contains("主要入驻企业"))
            {
                strErrors.Append("【主要入驻企业】");
            }
            if (!dt.Columns.Contains("其他配置"))
            {
                strErrors.Append("【其他配置】");
            }
            if (!dt.Columns.Contains("所属商圈"))
            {
                strErrors.Append("【所属商圈】");
            }
            if (!dt.Columns.Contains("周边商业配套"))
            {
                strErrors.Append("【周边商业配套】");
            }
            if (!dt.Columns.Contains("周边交通配套"))
            {
                strErrors.Append("【周边交通配套】");
            }
            if (!dt.Columns.Contains("物业满意度打分"))
            {
                strErrors.Append("【物业满意度打分】");
            }
            if (!dt.Columns.Contains("物业满意度报告"))
            {
                strErrors.Append("【物业满意度报告】");
            }
             
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel表数据不正常,缺少以下数据列:\r\n" +
                                    strErrors);
                return false;
            }
            return true;
        }
        #endregion
    }
}